﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;

namespace AngelCMS.module.DAO
{
    public class AccessHelper
    {
        protected  OleDbConnection conn = new OleDbConnection();
        protected  OleDbCommand comm = new OleDbCommand();

        public AccessHelper()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 打开数据库
        /// </summary>
        private  void openConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + ConfigurationManager.AppSettings["myconn"].ToString();
                comm.Connection = conn;
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                { throw new Exception(e.Message); }

            }

        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        private  void closeConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
                comm.Dispose();
            }
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sqlstr"></param>
        public  void excuteSql(string sqlstr)
        {
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            { closeConnection(); }
        }



        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sqlstr"></param>
        public  int  excuteSqlInt(string sqlstr)
        {
            int result = 0;
            try
            {
               
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                result= comm.ExecuteNonQuery();
               
            }
            catch (Exception e)
            {
                result = 0;
                throw new Exception(e.Message);
               
            }
            finally
            {
              
                closeConnection();
                
            }

            return result;
        }


        /// <summary>
        /// 返回指定sql语句的OleDbDataReader对象，使用时请注意关闭这个对象。
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public  OleDbDataReader dataReader(string sqlstr)
        {
            OleDbDataReader dr = null;
            try
            {
                openConnection();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;

                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    dr.Close();
                    closeConnection();
                }
                catch { }
            }
            return dr;
        }
        /// <summary>
        /// 返回指定sql语句的OleDbDataReader对象,使用时请注意关闭
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="dr"></param>
        public  void dataReader(string sqlstr, ref OleDbDataReader dr)
        {
            try
            {
                openConnection();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    if (dr != null && !dr.IsClosed)
                        dr.Close();
                }
                catch
                {
                }
                finally
                {
                    closeConnection();
                }
            }
        }
        /// <summary>
        /// 返回指定sql语句的dataset
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public  DataSet dataSet(string sqlstr)
        {
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                
                da.SelectCommand = comm;
                da.Fill(ds);

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return ds;
        }






        /// <summary>
        /// 返回指定sql语句的dataset
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="ds"></param>
        public  void dataSet(string sqlstr, ref DataSet ds)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
        }
        /// <summary>
        /// 返回指定sql语句的datatable
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public  DataTable dataTable(string sqlstr)
        {
            DataTable dt = new DataTable();
           // OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                OleDbDataAdapter da = new OleDbDataAdapter(comm);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return dt;
        }
        /// <summary>
        /// 返回指定sql语句的datatable
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="dt"></param>
        public  void dataTable(string sqlstr, ref DataTable dt)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
        }
        /// <summary>
        /// 返回指定sql语句的dataview
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public  DataView dataView(string sqlstr)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataView dv = new DataView();
            DataSet ds = new DataSet();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
                dv = ds.Tables[0].DefaultView;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return dv;
        }



        /// <summary>分页  
        ///   
        /// </summary>  
        /// <param name="tblName">表名</param>  
        /// <param name="fldName">字段名</param>  
        /// <param name="OrderfldName">排序字段名</param>  
        /// <param name="OrderType">排序方式：asc或者desc</param>  
        /// <param name="strWhere">条件，不用加where</param>  
        /// <param name="PageSize">页大小</param>  
        /// <param name="PageIndex">页索引</param>  
        /// <returns></returns>  
        public  DataTable FengYe(string tblName, string fldName, string OrderfldName, string OrderType, string strWhere, int PageSize, int PageIndex,out int count)
        {
            DataTable dt = new DataTable();
            DataTable dtCount = new DataTable();
            string strSQL = ""; // 主语句   
            string strcout = "";//获取总条数
            string strTmp = ""; // 临时变量   
            string strOrder = ""; // 排序类型  
            count = 0;

            if (OrderType == "asc")
            {
                // 升序  
                strTmp = ">(select max";
                strOrder = " order by " + OrderfldName + " asc";
            }
            else
            {             
                // 降序  
                strTmp = "<(select min";
                strOrder = " order by " + OrderfldName + " desc";
            }

            string wheresql= string.IsNullOrEmpty(strWhere) ? "" : " where " + strWhere;
              strcout = "SELECT Count(*) FROM  " + tblName + wheresql;
              dtCount = dataTable(strcout);
              count =Convert.ToInt32(dtCount.Rows[0][0]);
            

            #region 第一页  
            if (PageIndex == 1)
            {
                strTmp = string.IsNullOrEmpty(strWhere) ? "" : " where " + strWhere;
                strSQL = "select top " + PageSize + " " + fldName + " from " + tblName + strTmp + " " + strOrder;
                dt= dataTable(strSQL);
               
                return dt;
            }
            #endregion


            #region 不是第一页  
            if (string.IsNullOrEmpty(strWhere))
            {
                // 条件为空  
                strSQL = string.Format("select top {0} {1} from {2} where {3}{4}({5}) from (select top {6} {7} from {8} {9}) as tblTmp) {10}", PageSize, fldName, tblName, OrderfldName, strTmp, OrderfldName, (PageIndex - 1) * PageSize, OrderfldName, tblName, strOrder, strOrder);
                
                dt = dataTable(strSQL);
            }
            else
            {
                // 条件不为空  
                strSQL = string.Format("select top {0} {1} from {2} where  {3}{4}({5}) from (select top {6} {7} from {8} where {9} {10}) as tblTmp) and {11} {12}", PageSize, fldName, tblName, OrderfldName, strTmp, OrderfldName, (PageIndex - 1) * PageSize, OrderfldName, tblName, strWhere, strOrder, strWhere, strOrder);
              
                dt = dataTable(strSQL);
            }
            #endregion


            return dt;
        }

        
        

    }
}